package com.kaluli.project.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.LinkedList;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;


import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.Date;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class Read_excel_bb {
	//创建文件输入流
	private BufferedReader reader = null;
	//文件类型
	private String fileType;
	
	//文件二进制输入流
	private InputStream is = null;
	//当前sheet
	private int currSheet;
	//当前位置
	private int currPosition;
	//sheet数量
	private int numOfSheets;
	//HSSFWorkbook
	HSSFWorkbook workbook = null;
	//设置cell之间以空格分隔
	private static String EXCEL_LINE_DELIMITER = ",";
	//设置最大列数
	private static int MAX_EXCEL_COLUMNS = 64;
	
	//构造函数创建一个ExcelReader
	public Read_excel_bb(String inputfile) throws IOException,Exception {
		//判断参数是否为空或者没有意义
		if(null == inputfile && "".equals(inputfile.trim())) {
			throw new IOException("no input file specified");
		}
		//取得文件名后缀赋值给fileType
		this.fileType = inputfile.substring(inputfile.lastIndexOf(".")+1);
		//设置开始行为0
		currPosition = 1;
		//设置当前位置为0
		currSheet = 0;
		//创建输入流
		is = new FileInputStream(inputfile);
		//判断文件格式
		if(fileType.equalsIgnoreCase("txt")) {
			//如果是txt则直接创建BufferReader读取
			reader = new BufferedReader(new InputStreamReader(is));
		}
		else if(fileType.equalsIgnoreCase("xls")) {
			//如果是Excel文件则创建HSSFWorkbook读取
			workbook = new HSSFWorkbook(is);
			//设置sheet数
			numOfSheets = workbook.getNumberOfSheets();
		}else {
			throw new Exception("File Type not Supported");
		}
		
	}
	
	//函数readLine读取文本的一行
	public String readLine() throws IOException {
		//如果是txt则通过reader读取
		if(fileType.equalsIgnoreCase("txt")) {
			String str = reader.readLine();
			//空行则略去，直接读取下一行
			while(str.trim().equals("")) {
				str = reader.readLine();
			}
			return str;
		}
		//如果是xls文件则通过POI提供给的API读取文件
		else if(fileType.equalsIgnoreCase("xls")) {
			//根据currSheet值获得当前的sheet
			HSSFSheet sheet = workbook.getSheetAt(currSheet);
			//判断当前行是否到当前sheet的结尾
			if(currPosition > sheet.getLastRowNum()) {
				//当前行位置清零
				currPosition = 1;
				//判断是否还有Sheet
				while(currSheet != numOfSheets -1){
					if(currSheet==2){
						break;
					}
					//得到下一个sheet
					sheet = workbook.getSheetAt(currSheet+1);
					//判断当前行是否到当前sheet的结尾
					if(currPosition == sheet.getLastRowNum()) {
						currSheet++;
						continue;
					}else {
						//获取当前行数
						int row = currPosition;
						currPosition++;
						//读取当前行数据
//						getLineBytrade(sheet,row);
					}
				}
				return null;
			}
			//获取当前行数
			int row = currPosition;
			currPosition++;
			//读取当前行数据
			getLineBycomment(sheet,row);
		}
		return readLine();
	}
	//函数getLine返回sheet的一行数据
	private String getLineBycomment (HSSFSheet sheet,int row) {
		//根据行数取得sheet的一行
		HSSFRow rowLine = sheet.getRow(row);
		//创建字符串缓冲区
		StringBuffer buffer = new StringBuffer();
		buffer.append("insert into `kll_item_custom`(`id`,`reg_app_date`,`copgName`,`hs_code`,`stock_number`,`code`,`netWeight`,`brand`,`spec`,`original`,`unit`,`unit_code`,`grossWeight`,`unit_price`) values('0',");
		//获取挡前行的列数
		int filledColumns = rowLine.getLastCellNum();
		HSSFCell cell = null;
		String upStr="";//上一个单元格
		String product_id="";
		//循环遍历所有列
		for(int i=0;i<filledColumns;i++) {
			//取得当前cell
			cell = rowLine.getCell(i);
			String cellValue = null;
			if(null != cell) {
				//判断当前cell的type
				switch(cell.getCellType()) {
					//如果当前cell的type为NUMERIC
					case HSSFCell.CELL_TYPE_NUMERIC : {
						//判断当前cell是否为Date
						if(HSSFDateUtil.isCellDateFormatted(cell)){
							//如果是Date类型，取得该Cell的Date值
							Date date = cell.getDateCellValue();
							//把Date转换成本地格式的字符串
							cellValue = new  java.text.SimpleDateFormat( "yyyy-MM-dd HH:mm:ss" ).format(cell.getDateCellValue());
						}
						//如果是纯数字
						else {
							//取得当前cell的数值
							;//默认返回时double类型
							cellValue = String.valueOf(cell.getNumericCellValue());
						}
						break;
					}
					//如果当前cell的type为String
					case HSSFCell.CELL_TYPE_STRING :
						//取得当前shell的字符串
						cellValue = cell.getStringCellValue().replaceAll("/'", "/");
						break;
					//默认的cell值
					default:
						cellValue = "";
				}
			}else {
				cellValue = "";
			}
			
			if(i==12){
				buffer.append("'").append(cellValue).append("'").append(");").append("");
				System.out.println(buffer.toString().replaceAll("	", ""));
				return buffer.toString();
			}else{
				buffer.append("'").append(cellValue).append("'").append(EXCEL_LINE_DELIMITER);
			}
			upStr=cellValue;
		}
		
		System.out.println(buffer.toString().trim());
		//以字符串返回该行的数据
		return buffer.toString();
	}
	
	
	
	//close函数执行流的关闭操作
	public void close() {
		//如果id不为空，则关闭InputStream文件输入流
		if(is != null) {
			try {
				is.close();
			}catch(IOException e) {
				is = null;
			}
		}
		
		//如果reader不为空，则关闭BufferedReader文件输入流
		if(reader != null) {
			try {
				reader.close();
			}catch(IOException e) {
				reader = null;
			}
		}
	}
	public static void main(String[] args) throws IOException, Exception {
		String path = "e:/work/excel/customs.xls";
		Read_excel_bb excel = new Read_excel_bb(path);
		excel.readLine();
	}
}